*** Load data on EMBI and S&P Country ratings

use "./DataSkeleton/AssetClasses/clean_embi_rating.dta",clear
keep if sp!=.
encode country, gen(cid)
xtset cid date
* calculate monthly returns and betas 
gen rx=1200*(log(F28.embi)-log(embi))-libor
gen mkt=1200*(log(F28.sp)-log(sp))-libor
keep if rx!=. & mkt!=.
gen month=mofd(date)
format month %tm 
by cid: asreg rx mkt, window(date -300 0) 
save "./OutputInterim/EMBI/clean_beta.dta",replace


**** Form portfolio 
use "./OutputInterim/EMBI/clean_beta.dta",clear
sort cid date
collapse (lastnm) rx rating libor, by(country cid month)
save "./OutputInterim/EMBI/monthly_return_rating.dta",replace


use "./OutputInterim/EMBI/clean_beta.dta",clear
sort cid date
drop if _Nobs<100
collapse (lastnm) _b_mkt, by(cid month)
rename _b_mkt beta
mmerge cid month using "./OutputInterim/EMBI/monthly_return_rating.dta"
rename rating rate
keep if rx!=. & beta!=. & rate!=.
keep if month>=tm(1994m12)
gen rate_mod=rate+beta/10

by month, sort: egen beta_med = xtile(beta), nq(2)
by month beta_med, sort: egen rate_ctile=xtile(rate_mod), nq(3)

gen port=1 if beta_med==1 & rate_ctile==1
replace port=2 if beta_med==1 & rate_ctile==2
replace port=3 if beta_med==1 & rate_ctile==3
replace port=4 if beta_med==2 & rate_ctile==1
replace port=5 if beta_med==2 & rate_ctile==2
replace port=6 if beta_med==2 & rate_ctile==3
replace rx=rx/1200

collapse (mean) rx, by(port month)
reshape wide rx, i(month) j(port)
mmerge month using "./OutputInterim/EMBI/monthly_return_rating.dta", ukeep(libor) uif(country=="Brazil") unmatched(master)
rename libor rf 
label var rf "1M risk-free (1M libor)"
replace rf=rf/100
replace month=month+1 
drop  _merge
label var rx1 "Our P1 monthly excess return (t-1,t)"
label var rx2 "Our P2 monthly excess return (t-1,t)"
label var rx3 "Our P3 monthly excess return (t-1,t)"
label var rx4 "Our P4 monthly excess return (t-1,t)"
label var rx5 "Our P5 monthly excess return (t-1,t)"
label var rx6 "Our P6 monthly excess return (t-1,t)"
sort month
order month rf 
export delimited "./OutputInterim/EMBI/clean_soveriegn_portfolios.csv",replace

